package com.css.fxfzaqbz.modules.data.repository.impl;


import com.css.fxfzaqbz.constants.YNEnum;
import com.css.fxfzaqbz.modules.data.entity.Report;
import com.css.fxfzaqbz.modules.data.queryParams.ActMap;
import com.css.fxfzaqbz.modules.data.queryParams.ActReport;
import com.css.fxfzaqbz.modules.data.repository.ReportRepository;
import com.css.fxfzaqbz.modules.port.entity.ReportVO;
import com.css.fxfzaqbz.modules.port.param.ReportPortParam;
import com.css.fxfzaqbz.util.PlatformObjectUtils;
import com.css.fxfzaqbz.web.PlatformSessionContext;
import com.css.fxfzaqbz.zcpt.sys.service.SUserService;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class ReportRepositoryImpl implements ReportRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Resource
    SUserService sUserService;

    @Override
    public Report saveReport(Report report) {

        String sql = "insert into sys_report (" +
                "sr_id,sr_number,sr_name,sr_type,ping_jia,sr_create_time,sr_status,ping_status,cb_id,bm_id,ld_id,cb_status,bm_status,ld_status," +
                "del_flag,create_user,create_time,update_user,update_time,ia_id,attach_id,flag,province_id,city_id,counties_id" +
                ")values(" +
                ":srId,:srNumber,:srName,:srType,:pingJia," +
                ":srCreateTime,:srStatus,:pingStatus,:cbId,:bmId,:ldId,:cbStatus,:bmStatus,:ldStatus,:delFlag,:createUser," +
                ":createTime,:updateUser,:updateTime,:iaId,:attachId,:flag,:provinceId,:cityId,:countiesId)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(report);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return report;
    }

    @Override
    public List<Report> getAllReportById(String id) {
        StringBuilder sql = new StringBuilder(" select a.* from sys_report a ");
        StringBuilder whereSql = new StringBuilder(" where a.del_flag = '" + YNEnum.N.toString() + "'");
        if (!PlatformObjectUtils.isEmpty(id)) {
            whereSql.append(" and a.ia_id = '" + id + "'");
        }
        String wholeSql = sql.append(whereSql).toString();
        return jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(Report.class));
    }

    @Override
    public Report getReportById(String id) {
        String sql = "select * from sys_report where sr_id = '" + id + "';";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Report.class));
    }

    @Override
    public void delReports(List<String> ids, Timestamp timestamp, String userID) {
        String sql = "update sys_report set del_flag = '" + YNEnum.Y + "' , update_time = '" + timestamp + "' ,update_user = '" + userID + "'" + " where sr_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public Report updateReport(Report report) {
        String sql = "update sys_report set" +
                " sr_number = :srNumber, " +
                " sr_name = :srName," +
                " sr_type = :srType," +
                " sr_create_time = :srCreateTime," +
                " sr_status = :srStatus," +
                " ia_id = :iaId," +
                " del_flag = :delFlag," +
                " ping_jia = :pingJia," +
                " ping_status = :pingStatus," +
                " cb_id = :cbId," +
                " bm_id = :bmId," +
                " ld_id = :ldId," +
                " create_user = :createUser," +
                " create_time = :createTime," +
                " update_user = :updateUser, " +
                " update_time = :updateTime " +
                " where sr_id = '" + report.getSrId() + "'";


        SqlParameterSource source = new BeanPropertySqlParameterSource(report);
        namedParameterJdbcTemplate.update(sql, source);
        return report;
    }

    @Override
    public Map<String, Object> getActReport(ActReport actReport) {
        StringBuilder sql = new StringBuilder("select  r.sr_id,r.attach_id,r.sr_name,r.sr_type,r.flag='1',sr_create_time,i.ia_id,i.ia_name,i.province_id,i.city_id,i.counties_id,i.ia_province,i.ia_city,i.ia_counties,i.ia_activity_status " +
                "from  sys_report r LEFT JOIN sys_important_activity i on i.ia_id=r.ia_id ");
        StringBuilder whereSql = new StringBuilder("where r.del_flag='0' and i.del_flag='0' and i.ia_activity_status ='1' and r.flag='1'");
        if (!PlatformObjectUtils.isEmpty(actReport.getProvinceId())) {
            whereSql.append(" and r.province_id = '" + actReport.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(actReport.getCityId())) {
            whereSql.append(" and r.city_id = '" + actReport.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(actReport.getCountiesId())) {
            whereSql.append(" and r.counties_id = '" + actReport.getCountiesId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(actReport.getSrType())) {
            whereSql.append(" and r.sr_type = '" + actReport.getSrType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(actReport.getIaName())) {
            whereSql.append(" and i.ia_name like '%" + actReport.getIaName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(actReport.getSrName())) {
            whereSql.append(" and r.sr_name like '%" + actReport.getSrName() + "%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by sr_create_time desc");
        int firstIndex = (actReport.getCurPage() - 1) * actReport.getPageSize();
        int lastIndex = actReport.getPageSize();
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql).toString();
        System.out.println(sql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ActReport.class)).size();
        List<ActReport> actReports = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ActReport.class));
        Map<String, Object> map = new HashMap<>();
        map.put("total", total);
        map.put("actReports", actReports);
        return map;
    }

    @Override
    public Map<String, Object> getActMap(ActMap actMap) {
        StringBuilder sql = new StringBuilder("SELECT * from (SELECT r.sra_id as t_id, r.sra_ass_file_id as attach_id,r.sra_ass_file_titel as t_name,r.types,r.sra_ass_content as t_content,r.sra_ass_date as t_create_time, i.ia_id ,i.ia_name,i.ia_province,i.ia_city,i.ia_counties,i.province_id,i.city_id,i.counties_id,i.ia_activity_status from sys_risk_assessment r LEFT JOIN sys_important_activity i on r.sra_relation=i.ia_id where r.del_flag='0' and i.del_flag='0' and r.sra_ass_file_id !='' union " +
                "SELECT d.sda_id as t_id, d.sda_ass_file_id as attach_id,d.sda_ass_file_titel as t_name,d.types,d.sda_ass_sei_intensity as t_content,d.sda_ass_date as t_create_time, i.ia_id ,i.ia_name,i.ia_province,i.ia_city,i.ia_counties,i.province_id,i.city_id,i.counties_id,i.ia_activity_status from sys_disaster_assessment d LEFT JOIN sys_important_activity i on d.sda_relation =i.ia_id where d.del_flag ='0' and i.del_flag='0' union " +
                "SELECT c.sc_id as t_id, c.sc_file_id as attach_id,c.sc_file_title as t_name,c.types,c.sc_ass_content as t_content,c.sc_date as t_create_time, i.ia_id ,i.ia_name,i.ia_province,i.ia_city,i.ia_counties,i.province_id,i.city_id,i.counties_id,i.ia_activity_status from sys_casualties c LEFT JOIN sys_important_activity i on c.sc_relation = i.ia_id where c.del_flag ='0' and i.del_flag ='0' union " +
                "SELECT s.ssd_id as t_id, s.ssd_image_id as attach_id,s.ssd_image_title as t_name,s.types,s.ssd_content as t_content,s.ssd_date as t_create_time, i.ia_id ,i.ia_name,i.ia_province,i.ia_city,i.ia_counties,i.province_id,i.city_id,i.counties_id,i.ia_activity_status from sys_second_disasters s LEFT JOIN sys_important_activity i on s.ssd_relation = i.ia_id where s.ssd_del_flag ='0' and i.del_flag ='0' and s.ssd_image_id !='' union " +
                "SELECT k.kr_id as t_id, k.picture_id as attach_id,k.title as t_name,k.types,k.kr_content as t_content,k.kr_date as t_create_time, i.ia_id ,i.ia_name,i.ia_province,i.ia_city,i.ia_counties,i.province_id,i.city_id,i.counties_id,i.ia_activity_status from sys_keyplace_riskassessment k LEFT JOIN sys_important_activity i on k.kr_relation = i.ia_id where k.del_flag ='0' and i.del_flag ='0') w");
        StringBuilder whereSql = new StringBuilder(" where ia_activity_status='1'");
        if (!PlatformObjectUtils.isEmpty(actMap.getProvinceId())) {
            whereSql.append(" and province_id = '" + actMap.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(actMap.getCityId())) {
            whereSql.append(" and city_id = '" + actMap.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(actMap.getCountiesId())) {
            whereSql.append(" and counties_id = '" + actMap.getCountiesId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(actMap.getIaName())) {
            whereSql.append(" and ia_name like '%" + actMap.getIaName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(actMap.getTName())) {
            whereSql.append(" and t_name like '%" + actMap.getTName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(actMap.getTContent())) {
            whereSql.append(" and t_content like '%" + actMap.getTContent() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(actMap.getTypes())) {
            whereSql.append(" and types = '" + actMap.getTypes() + "'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t_create_time desc");
        int firstIndex = (actMap.getCurPage() - 1) * actMap.getPageSize();
        int lastIndex = actMap.getPageSize();
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql).toString();
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ActReport.class)).size();
        List<ActMap> actMaps = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(ActMap.class));

        Map<String, Object> map = new HashMap<>();
        map.put("total", total);
        map.put("actMaps", actMaps);
        return map;
    }

    @Override
    public String getReportByFC(Report report) {
        try {
            String sql = "select sr_number from sys_report where flag = '" + report.getFlag() + "' and sr_type ='" + report.getSrType() + "' and del_flag = '" + YNEnum.N.toString() + "' and ia_id = '" + report.getIaId() + "'";
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public String getNewCode(String iaId, Timestamp startTimestamp, Timestamp endTimestamp) {
        try {
            String sql = "select count(*) from sys_report where create_time between '" + startTimestamp + "' and '" + endTimestamp + "' and del_flag = '" + YNEnum.N.toString() + "' and ia_id = '" + iaId + "'";

            String s = jdbcTemplate.queryForObject(sql, String.class);
            return s;
        } catch (DataAccessException e) {
            return "0";
        }
    }

    @Override
    public Report getReportByFCI(Report report) {
        try {
            String sql = "select * from sys_report where flag = '" + report.getFlag() + "' and sr_type ='" + report.getSrType() + "' and del_flag = '" + YNEnum.N.toString() + "' and ia_id = '" + report.getIaId() + "'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Report.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<Report> getHistory(String iaId) {

        String sql = "select * from sys_report where ia_id = '" + iaId + "'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Report.class));
    }

    @Override
    public List<Report> getAllReport(Report report) {
        StringBuilder sql = new StringBuilder(" select  u.* from sys_report u ");

       StringBuilder orderSql = new StringBuilder(" order by u.create_time desc");

        StringBuilder whereSql = new StringBuilder(" where u.del_flag = '" + YNEnum.N.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(report.getProvinceId())) {
            whereSql.append(" and u.province_id ='"+report.getProvinceId()+"'");
        }

        if (!PlatformObjectUtils.isEmpty(report.getCityId())) {
            whereSql.append(" and u.city_id  ='" + report.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(report.getCountiesId())) {
            whereSql.append(" and u.counties_id ='" + report.getCountiesId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(report.getSrName())) {
            whereSql.append(" and u.sr_name like '%" + report.getSrName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(report.getSrType())) {
            whereSql.append(" and u.sr_type like '%" + report.getSrType() + "%'");
        }
        String userID = PlatformSessionContext.getUserID();
//获取当前登录人


//判断当前登录人是属于 初级审核人员 还是部门审核人员 还是领导审核人员
//一个登录人可以是多个 角色 比如在一个活动中是初级  在另一个活动中是部门
//


            whereSql.append(" and ( u.cb_id = '"+userID+"' or  u.bm_id ='"+userID+"'  or  u.ld_id='"+userID+"') ");


        sql.append(whereSql).append(orderSql).toString();
        List<Report> reportList = null;
        try {
            reportList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Report.class));
        } catch (DataAccessException e) {
           return null;
        }
        return reportList;
    }

    @Override
    public Report updateReportStatus(Report report) {
        String sql = "update sys_report set" +
                " sr_status = :srStatus, " +
                " cb_status = :cbStatus," +
                " cb_reason = :cbReason," +
                " cb_date = :cbDate," +
                " bm_status = :bmStatus," +
                " bm_reason = :bmReason," +
                " bm_date = :bmDate," +
                " ld_status = :ldStatus," +
                " ld_reason = :ldReason," +
                " ld_date = :ldDate," +
                "mark =:mark" +
                " where sr_id = '" + report.getSrId() + "'";


        SqlParameterSource source = new BeanPropertySqlParameterSource(report);
        namedParameterJdbcTemplate.update(sql, source);
        return report;
    }

    @Override
    public String updateZiStatus(Report report) {
        String sql = "update sys_report set" +
                " ping_status = :pingStatus, " +
                " ping_jia = :pingJia" +
                " where sr_id = '" + report.getSrId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(report);
        namedParameterJdbcTemplate.update(sql, source);
        return report.getPingJia();
    }

    @Override
    public void updatePsStatus(String s, String srId) {
        String sql="update sys_report set ping_status ='"+s+"' where sr_id='"+srId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void submitSh(Report report) {
        String sql = "update sys_report set" +
                " sr_status = :srStatus, " +
                " cb_status = :cbStatus," +
                " bm_status = :bmStatus," +
                " ld_status = :ldStatus," +
                " submit_time = :submitTime," +
                " submiter = :submiter" +
                " where sr_id = '" + report.getSrId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(report);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void uploadYiJian(Report report) {
        String sql = "update sys_report set" +
                " ping_yijian = :pingYijian, " +
                " ping_yijian_name = :pingYijianName, " +
                " sr_number=:srNumber, "+
                " sr_name=:srName "+
                " where sr_id = '" + report.getSrId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(report);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void reUpload(Report report) {
        String sql = "update sys_report set" +
                " attach_id = :attachId, " +
                " sr_name = :srName " +
                " where sr_id = '" + report.getSrId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(report);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public Report getReportByIaId(String id) {
        try {
            String sql = "select * from sys_report where ia_id = '" + id + "' and del_flag = '"+YNEnum.N+"' and flag= '"+YNEnum.N+"'";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Report.class));
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Override
    public List<ReportVO> getReportPort() {
        StringBuilder sql = new StringBuilder(" select  u.*,a.ia_name  from sys_report u LEFT JOIN sys_important_activity a on u.ia_id = a.ia_id");

        StringBuilder orderSql = new StringBuilder(" order by u.create_time desc");

        StringBuilder whereSql = new StringBuilder(" where u.del_flag = '" + YNEnum.N.toString() + "'");

        whereSql.append(" and  u.flag = '"+YNEnum.Y+"' and  u.ping_status ='"+2+"' ");

        List<ReportVO> reportVOS = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(ReportVO.class));

        return reportVOS;

    }

    @Override
    public List<Report> getReport1ByIaId(String iaId) {
        String sql = "select * from sys_report where ia_id = '" + iaId + "' and del_flag = '"+YNEnum.N+"' and flag= '"+YNEnum.Y+"'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Report.class));
    }


}
